package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import dto.AnswersDto;

public class AnswersDao {

	public String insertAnswer(AnswersDto answer) {

		if (answer == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO answers (answer_no, case_a, case_b, case_c, case_d, questions_question_no, answer) VALUES (?,?,?,?,?,?,?)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setInt(1, answer.getAnswerNo());
			preparedStatement.setString(2, answer.getCaseA());
			preparedStatement.setString(3, answer.getCaseB());
			preparedStatement.setString(4, answer.getCaseC());
			preparedStatement.setString(5, answer.getCaseD());
			preparedStatement.setInt(6, answer.getQuestionNo());
			preparedStatement.setInt(7, answer.getAnswer());

			preparedStatement.execute();
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public String deleteAnswer(String answer_no) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "DELETE FROM answers WHERE answer_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, answer_no);

			preparedStatement.execute();
			response = "Deleted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Delete failed! " + e.getMessage();
		}

		return response;
	}

	public AnswersDto getAnswerById(String answer_no) {
		AnswersDto answer = null;

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM answers WHERE answer_no = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, answer_no);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				answer = new AnswersDto();
				answer.setAnswerNo(resultSet.getInt("answer_no"));
				answer.setCaseA(resultSet.getString("case_a"));
				answer.setCaseB(resultSet.getString("case_b"));
				answer.setCaseC(resultSet.getString("case_c"));
				answer.setCaseD(resultSet.getString("case_d"));
				answer.setQuestionNo(resultSet.getInt("questions_question_no"));
				answer.setAnswer(resultSet.getInt("answer"));
			}

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return answer;
	}

	public ArrayList<AnswersDto> getAllAnswer() {

		ArrayList<AnswersDto> listAnswer = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM answers";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				AnswersDto answer = new AnswersDto();
				answer.setAnswerNo(resultSet.getInt("answer_no"));
				answer.setCaseA(resultSet.getString("case_a"));
				answer.setCaseB(resultSet.getString("case_b"));
				answer.setCaseC(resultSet.getString("case_c"));
				answer.setCaseD(resultSet.getString("case_d"));
				answer.setQuestionNo(resultSet.getInt("questions_question_no"));
				answer.setAnswer(resultSet.getInt("answer"));
				listAnswer.add(answer);
			}

			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listAnswer;
	}

}
